package com.sf.mysql.tool.excel;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.util.*;

/**
 * @author Administrator  2017/12/10.
 */
public class ReadExcelUtil {

    private static final  int START = 0;

    /**
     * 获取Excel文件的Workbook对象
     * @param fileName 文件路径
     * @return
     * @throws Exception
     */
    public static Workbook getWorkBook(String fileName) throws Exception {
        long startTime = System.currentTimeMillis();
        Workbook wb = null;
        try {
            wb = new HSSFWorkbook(new FileInputStream(fileName));
        } catch (Exception e) {
            wb = new XSSFWorkbook(new FileInputStream(fileName));
        }
        System.out.println(System.currentTimeMillis() - startTime);
        return wb;
    }

    /**
     * 读取指定Sheet页的所有内容
     * @param sheet 页
     * @return
     */
    public static List<List<String>> readFromSheet(Sheet sheet) {
        if(sheet == null) {
            throw new NullPointerException("sheet not be null");
        }

        //保存所有读取到的数据
        List<List<String>> dataList = new ArrayList<>();

        //遍历行
        for (Row row : sheet) {
            List<String> rowDataList = new ArrayList<>();
            //遍历列
            for (Cell cell : row) {
                rowDataList.add(getCellValue(cell));
            }
            dataList.add(rowDataList);
        }
        return dataList;
    }

    /**
     * 读取指定Sheet页的所有内容
     * @param wb workbook
     * @param sheetName sheet名
     * @return
     */
    public static List<List<String>> readFromSheet(Workbook wb, String sheetName) {
        Sheet sheet = wb.getSheet(sheetName);
        return readFromSheet(sheet);
    }


    /**
     * 获取Cell对象表示的值
     * @param cell 单元格
     * @return
     */
    private static String getCellValue(Cell cell) {
        if(cell == null) {
            return "";
        }
        String tempValue = null;
        //判断Cell的类型
        switch (cell.getCellTypeEnum()) {
            case STRING:
                tempValue = cell.getRichStringCellValue().getString();
                break;

            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    tempValue = cell.getDateCellValue().toString();
                } else {
                    tempValue = String.valueOf(cell.getNumericCellValue());
                }
                break;

            case BOOLEAN :
                tempValue = String.valueOf(cell.getBooleanCellValue());
                break;

            case FORMULA :
                tempValue = cell.getCellFormula();
                break;

            default:
                tempValue = "";
                break;
        }
        return tempValue;
    }

    /**
     * 获取有用数据所在的列
     * @param tempValueList 临时表
     * @param includeColNameSet 存在的colName set
     * @return
     */
    private static int getColIndex(List<String> tempValueList,
                                   Set<String> includeColNameSet) {
        for(int i = 0, len = tempValueList.size(); i < len; i++) {
            //如果包含指定的列名
            if(includeColNameSet.contains(tempValueList.get(i))) {
                if(isTruePosition(tempValueList, i, includeColNameSet)) {
                    return i;
                }
            }
        }
        return START;
    }

    /**
     * 判断当前行所在数据是否是有用数据所在的行
     * @param tempValueList 临时表
     * @param start 起始位置
     * @param includeColNameSet 存在的colName set
     * @return
     */
    private static boolean isTruePosition(List<String> tempValueList, int start,Set<String> includeColNameSet) {
        boolean isPosition = true;
        if(tempValueList.size() < start + includeColNameSet.size()) {
            isPosition = false;
            return isPosition;
        }
        for(int i = start, len = start + includeColNameSet.size(); i < len; i++) {
            if(!includeColNameSet.contains(tempValueList.get(i))) {
                isPosition = false;
                break;
            }
        }
        return isPosition;
    }

    /**
     * 判断两行数据是否相同
     * @param valueList 被对比的数据
     * @param compareValueList 用来对比的数据
     * @return
     */
    private static boolean isEquals(List<String> valueList, List<String> compareValueList) {
        boolean isEnd = true;
        int len = valueList.size() > compareValueList.size() ? compareValueList.size() : valueList.size();
        for(int i = 0; i < len; i++) {
            if(!compareValueList.get(i).equals("")
                    && !valueList.get(i).equals(compareValueList.get(i))) {
                isEnd = false;
            }
        }
        return isEnd;
    }

}
